from django.db import models

# Create your models here.
# CREATE TABLE `employees` (
#   `emp_no` int(11) NOT NULL,
#   `birth_date` date NOT NULL,
#   `first_name` varchar(14) NOT NULL,
#   `last_name` varchar(16) NOT NULL,
#   `gender` smallint(6) NOT NULL DEFAULT '1' COMMENT 'M=1, F=2',
#   `hire_date` date NOT NULL,
#   PRIMARY KEY (`emp_no`)
# ) ENGINE=InnoDB DEFAULT CHARSET=utf8;


class Employee(models.Model):
    emp_no = models.IntegerField(primary_key=True)
    birth_date = models.DateField(null=False)
    first_name = models.CharField(null=False, max_length=14)
    last_name = models.CharField(null=False, max_length=16)
    gender = models.SmallIntegerField(null=False, default=1)
    hire_date = models.DateField(null=False)

    class Meta:
        db_table = "employees"

    @property
    def name(self):
        return f"{self.first_name}-{self.last_name}"

    def __str__(self):
        return f"{self.emp_no}----{self.name}"





# CREATE TABLE `titles_new` (
#   `id` int(11) NOT NULL AUTO_INCREMENT,
#   `title` varchar(50) NOT NULL,
#   PRIMARY KEY (`id`)
# ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

class Titles(models.Model):
    id = models.IntegerField(primary_key=True)
    title = models.CharField(max_length=50, null=False)

    class Meta:
        db_table = 'titles_new'

    def __str__(self):
        return f"T---{self.id}={self.title}"





#   CREATE TABLE `emp_title` (
#   `id` int(11) NOT NULL AUTO_INCREMENT,
#   `emp_no` int(11) DEFAULT NULL,
#   `title_id` int(11) DEFAULT NULL,
#   `from_date` date DEFAULT NULL,
#   `to_date` date DEFAULT NULL,
#   PRIMARY KEY (`id`),
#   KEY `emp_1` (`emp_no`),
#   KEY `title_1` (`title_id`),
#   CONSTRAINT `emp_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE,
#   CONSTRAINT `title_1` FOREIGN KEY (`title_id`) REFERENCES `titles_new` (`id`) ON DELETE CASCADE
# ) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8mb4;


class Emp_title(models.Model):
    id = models.IntegerField(primary_key=True)
    emp_no = models.ForeignKey(to=Employee, on_delete=models.CASCADE, null=False, db_column='emp_no' )
    title_id = models.ForeignKey(to=Titles, on_delete=models.CASCADE, null=False, db_column='title_id')
    from_date = models.DateField()
    to_date = models.DateField()

    class Meta:
        db_table = 'emp_title'

    @property
    def date1(self):
        return f"{self.from_date}--{self.to_date}"

    def __str__(self):
        return f"{self.emp_no_id}---{self.title_id_id}:{self.date1}"